The present invention relates to translating relations in databases to networked, persistent objects in a manner consistent with an expert, automated process, specifically with nominal computer to human interaction and minimal knowledge and training requirements from the standpoint of the end user. Once created, the present invention efficiently transmits the objects from databases in server computers, and in particular to a method and apparatus for making objects from databases in the classes of objects desired and to transmit the objects made, efficiently to client computers.
This application incorporates by reference the files on a Compact Disc Recordable (CD-R) media, for operating under IBM-PC machine format and MS-Windows operating system. The files are for execution by any machine supporting a lava Virtual Machine (JVM) and are thus platform independent. The list of files contained on the CD-R media, including the names, sizes in bytes and dates of creation is as follows:
SQL-based databases and the tables and structures contained therein are well known in the art. Typically, SQL-based tables and associated relations are xe2x80x9cflatxe2x80x9d structures involving elements in rows and columns with elements in a column xe2x80x9crelatedxe2x80x9d to elements in different columns by a relation.
Structured Query Language or xe2x80x9cSQLxe2x80x9d is used to define database elements, consisting, but not limited to: tables, columns with tables, data types of columns, relationships between tables, constraints of numerous types, and to perform queries upon and to also perform create, update, delete operations upon the aforementioned elements. Although attempts have been made at standardization, in reality the syntax of SQL and operation of relational databases can vary significantly from one database vendor and type to another. It can thus be problematic, within an application, to change from one database type to another.
The process of interrogation of relational database schema or catalogs to obtain information pertaining to the database tables and the interrelationships between database tables is well known.
The use of Internet, or Intranet, or other network to communicate from a database computer to a server computer to a client computer is also well known.
The use of software to manually map database tuples (rows of a table or, more importantly, multiple rows of related tables) into objects for use by object oriented languages such as lava and C++ is also well known.
The use of software to map objects from relations and data in relational database management systems or vice versa to object oriented applications is also well known.
The use of software to transmit information in object form from a server computer to a client computer or vice versa is also well known.
In the prior art, such as that disclosed in U.S. Pat. No.5,857,197, the process of manually mapping database tuples into objects is typically performed through utilization of graphical computer interface. Using a graphical computer interface in a manual manner for this relational to object mapping operation has proven to be time consuming and error prone. An expert-level technician with extensive knowledge of both the internals of relational databases and detailed knowledge and experience with object oriented systems and languages is typically required to use these software products, referred to as xe2x80x9cobject-relational mapping toolsxe2x80x9d. These expert-level personnel are usually in practice, both scarce and expensive.
In the prior art, databases have been maintained on server computers and when queried by a client computer the resultant objects have been transmitted to the client computer. There is an inherent mismatch between data stored in relational databases and the format and structure of this relational data in object based systems. The problem faced by the prior art and how such prior art has failed is detailed in xe2x80x9cRelational Data Hits the Highway, Making Persistent Objects from Relational Dataxe2x80x9d, Miller, Julia K. and Kern, Thomas, pgs. 38-42, Distributed Computing, Jul. 1998. Further, network efficiency problems faced by the prior art are enumerated in xe2x80x9cReducing Network Traffic in Distributed Java Appsxe2x80x9d, Patten, Bon and McCabe, James, pgs. 51-57, Java Report, Sep. 1999. Attempts to solve these problems in the prior art have been addressed by several methods. A common technique is to create an intermediate translation or mapping between the relational database(s) and the object system though interaction with a graphical interface. It is not uncommon to have to manually create or define all of the attributes and methods of the target objects, then manually map the corresponding relational data. This intermediate translation layer of software either significantly and measurably reduced the efficiency of the resultant application and/or introduced an additional point of failure into the application, thus reducing the overall reliability of the application.
In the prior art, manual creation or manipulation of SQL is also typically needed to populate the objects from the database though this intermediate object-relational mapping layer. This SQL can be specific to the brand or vendor of the database, making migration from one database type to that of another vendor costly and problematic. In the prior art, the combination of numerous, heterogeneous databases from different database vendors was either time-consuming, error-prone, problematic, inefficient, or not possible.
In the prior art, one could typically update the underlying relational database(s) exclusively through the object system, precluding direct database updates though conventional methods, such as offline or batch database load jobs or tasks, and realtime data communication software. The result would be data presented to end users that would not be current or, worse, when updates were made to the underlying database, the offline or batch changes would be lost.
In the prior art, to transmit subsets of related database tables as a distributed objects comprising, e.g. 4xc3x975 or a total of 20 elements, 4 objects need to be created at the server computer and transmitted to the client computer with up to, in the case of CORBA, 20 subsequent attribute requests from the client computer to the server computer interspersed there between for a total of 40 transmissions, or more, over the network to populate the 4 objects. This is wasteful of the network and server resources and reduces the performance and scalability of object systems so constructed. Thus, what is required and would be particularly useful would be:
1. A computer program product that embodies and contains the human knowledge of an expert in the fields of both relational databases and object systems and object languages, and,
2. A computer program product that reduces or eliminates the manual and error-prone manual mapping operations between the relational database(s) and the object oriented classes required by the object-based system, and,
3. A computer program product that provides the ability to combine multiple types of heterogeneous databases from different vendors into one logical object view, so that the business application software sees one logical set of objects in a vendor-independent manner, and,
4. A computer program product that provides a transparent way to update and access relational databases as persistent objects without the need to ever use or be aware of the specific query language (or SQL) unique to a given database vendor, thus providing the capability to change vendors and types of a given relational database so as to preclude the necessity to alter the high-level source code at the application level, and
5. A computer program product that permits offline database loads and batch database update jobs and real time data communications interface software to execute concurrently with, and update the same databases as, the object based system, whilst concurrently providing consistent and current data to the end-users and eliminating the possibility of lost updates, whilst also performing this in an efficient manner, and,
6. A computer program product that generates software that, at execution time, is as efficient or more efficient, and also less problematic, than software that could be manually designed, constructed, written, and tested by an expert in the fields of relational databases and object systems and object languages.
In the present invention, an automated, expert method, system and program product that translates and transmits metadata and data from database tables into familiar and customary objects desired is disclosed. The method comprises the steps of reading the definitional elements of the databases to determine data types and interrelationships between relational data elements. These data interrelationships and data types are assembled in a vendor-neutral standardized view of the database schemas and the plurality of all the possible logical objects contained therein in the databases are created. Template definitions generically represent the classes of the objects desired.
An inexperienced user can, if so desired, easily select a subset of all possible objects represented by the databases through use of a simple and intuitive graphical interface. Conversely, tables and interrelationships not required by the application can be easily deselected through use of the said simple and intuitive graphical interface.
Source code for the classes is then generated from the standardized view when merged with the prepared template definitions. The source code is then compiled into binary executable form into the classes desired. Pseudo-objects are then produced by dynamic generation and execution of pre-optimized SQL, enveloping values that result from execution of the generated prepared SQL statements. Result sets from said associated prepared statement operations from the appropriate database tables and rows are normalized into a standard format, then combined with metadata from the database schemas. The pseudo-objects are then ready for transmission to the client computer or the requester of the objects desired. The present invention also relates to a method of communicating elements of a database table between a server computer and a client computer. A pseudo-object is generated by the server computer with the pseudo-object comprising rows from singular database tables, or optimized joins between multiple related database tables, that comprise the object desired. The plurality of datatypes present in the relational databases are normalized into a singular standardized form to prepare the data for transmission to the requestor of the object. Metadata of the elements where the metadata is the relationship between the data elements is also generated by the server computer. The metadata and normalized pseudo-object data are transmitted from the server computer to the client computer in a single logical transmission.. At the client computer, the elements are assembled into the final objects from the pseudo-objects and metadata received, into the format required by the software on the client computer without runtime overhead on the database or middle-tier server computers.
The present invention also relates to a method of communication of new objects from client computers and their conversion into one or more rows to be inserted in the corresponding databases in transactional mode.
The present invention also relates to a method of communication of changes to existing objects from client computers and their conversion into updates to one or more rows so as to modify the rows of the appropriate tables in the corresponding databases in transactional mode.
The present invention also relates to a method of communication removal existing objects from client computers so as to delete the rows of the appropriate tables in the corresponding databases in transactional mode.
The present invention also relates to a method that permits the databases to be shared with multiple applications, so as to ensure that the most current data is presented to the end-users and that updates from end users do not collide with updates, inserts and deletes to database columns and rows performed outside of the object system.
Finally, the present invention also relates to an article of manufacture embodied as a computer usable medium having computer readable program code embodied therein configured to perform the aforementioned methods.